﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading;

namespace BISTools.DBTools
{
    /// <summary>
    /// 数据库操作准备类
    /// </summary>
    public class SqlPrepared
    {
        /// <summary>
        /// 获取拼接当前线程名称的缓存键值
        /// </summary>
        /// <param name="ora">原始字符串</param>
        /// <returns></returns>
        public static string GetPreparedNameWithThreadNameString(string ora)
        {
            return ora + Thread.CurrentThread.Name;
        }

        #region 查询部分

        /// <summary>
        /// 查询临时表
        /// </summary>
        protected Dictionary<string, SqlPreparedQueryData> mTableSet = new Dictionary<string, SqlPreparedQueryData>(8);

        /// <summary>
        /// 判断查询临时表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool ContainsKeyForQueryTable(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("获取临时表时表名不合法");
            }
            return this.mTableSet.ContainsKey(tableName);
        }

        /// <summary>
        /// 根据表名称获取查询临时表
        /// </summary>
        /// <param name="tableName">内存临时表名称</param>
        /// <returns></returns>
        public DataTable GetDataTableByTableName(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("获取临时表时表名不合法");
            }
            if (!mTableSet.ContainsKey(tableName))
            {
                throw new Exception("没有找到对应的内存临时表，表：" + tableName);
            }

            return this.mTableSet[tableName].Table;
        }

        /// <summary>
        /// 根据表名称获取查询临时表
        /// </summary>
        /// <param name="tableName">内存临时表名称</param>
        /// <returns></returns>
        public SqlPreparedQueryData GetSqlPreparedQueryDataByTableName(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("获取临时表时表名不合法");
            }
            if (!mTableSet.ContainsKey(tableName))
            {
                throw new Exception("没有找到对应的内存临时表，表：" + tableName);
            }

            return this.mTableSet[tableName];
        }

        /// <summary>
        /// 添加一个DataTable（如果存在则抛出异常）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataTable">数据表</param>
        public void AddDataTableWithNoReplace(string tableName, DataTable dataTable)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("添加临时表时表名不合法");
            }
            if (dataTable == null)
            {
                throw new Exception("添加临时表时表内容为空");
            }

            if (mTableSet.ContainsKey(tableName))
            {
                throw new Exception("添加临时表时存在相同的表名，表：" + tableName);
            }
            this.mTableSet.Add(tableName, new SqlPreparedQueryData(dataTable));
        }

        /// <summary>
        /// 添加一个DataTable（如果存在则替换 ）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataTable">数据表</param>
        public void AddDataTableWithReplace(string tableName, DataTable dataTable)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("添加临时表时表名不合法");
            }
            if (dataTable == null)
            {
                throw new Exception("添加临时表时表内容为空");
            }
            if (mTableSet.ContainsKey(tableName))
            {
                this.mTableSet[tableName] = new SqlPreparedQueryData(dataTable);
            }
            else
            {
                this.mTableSet.Add(tableName, new SqlPreparedQueryData(dataTable));
            }
        }

        /// <summary>
        /// 根据查询条件获取一个临时表的查询数据
        /// </summary>
        /// <param name="tableName">要查询的临时表名称</param>
        /// <param name="queryBuilder">要查询的条件</param>
        /// <returns></returns>
        public DataRow[] GetDataByQuery(string tableName, string queryBuilder)
        {
            if (string.IsNullOrEmpty(queryBuilder))
            {
                throw new Exception("查询条件不合法!");
            }

            DataTable tab = this.GetDataTableByTableName(tableName);

            return tab.Select(queryBuilder); ;
        }

        /// <summary>
        /// 根据主键值的相等条件获取单一行，没找到返回空
        /// </summary>
        /// <param name="tableName">要查询的临时表名称</param>
        /// <param name="keyColName">主键列名</param>
        /// <param name="value">主键值</param>
        /// <returns></returns>
        public DataRow GetSingleDataByQueryByPKDecimal(string tableName, string keyColName, decimal value)
        {
            DataTable tab = this.GetDataTableByTableName(tableName);
            DataRow r = null;
            foreach (DataRow row in tab.Rows)
            {
                if (Convert.ToDecimal(row[keyColName]) == value)
                {
                    r = row;
                    break;
                }
            }
            return r;
        }

        #region 根据主键值的相等条件获取单一行，没找到返回空【根据条件进行排序】

        /// <summary>
        /// 根据主键值的相等条件获取单一行，没找到返回空【根据条件进行排序】
        /// </summary>
        /// <param name="tableName">要查询的临时表名称</param>
        /// <param name="keyColName">主键列名</param>
        /// <param name="value">主键值</param>
        /// <param name="orderCondition">需要排序的条件</param>
        /// <returns></returns>
        public DataRow GetSingleDataByQueryByPKDecimalOrder(string tableName, string keyColName, decimal value, string orderCondition)
        {
            DataTable tab = this.GetDataTableByTableName(tableName);
            //排序
            DataView dv = tab.DefaultView;
            dv.Sort = orderCondition;
            DataTable tabNew = tab.Copy();
            tabNew = dv.ToTable();
            DataRow r = null;
            foreach (DataRow row in tabNew.Rows)
            {
                if (Convert.ToDecimal(row[keyColName]) == value)
                {
                    r = row;
                    break;
                }
            }
            return r;
        }

        #endregion 根据主键值的相等条件获取单一行，没找到返回空【根据条件进行排序】

        #region 根据获取的IList<DataRow>，进行排序后返回DataTable

        /// <summary>
        /// 根据获取的DataRow，进行排序后返回DataTable
        /// </summary>
        /// <param name="rows"></param>
        /// <param name="orderCondition">排序条件</param>
        /// <returns></returns>
        public DataTable GetDataTableByListTable(IList<DataRow> rows, string orderCondition)
        {
            DataTable tab = new DataTable();
            tab = rows[0].Table.Clone();
            foreach (DataRow row in rows[0].Table.Rows)
            {
                tab.ImportRow(row);
            }
            DataView dv = tab.DefaultView;
            dv.Sort = orderCondition;
            DataTable tabNew = tab.Clone();
            tabNew = dv.ToTable();
            return tabNew;
        }

        #endregion 根据获取的IList<DataRow>，进行排序后返回DataTable

        /// <summary>
        /// 根据查询条件获取一个临时表的查询数据
        /// </summary>
        /// <param name="tableName">要查询的临时表名称</param>
        /// <param name="queryBuilder">要查询的条件</param>
        /// <param name="sort">排序条件 eg: A1 desc,A3 asc</param>
        /// <returns></returns>
        public DataRow[] GetDataByQuery(string tableName, string queryBuilder, string sort)
        {
            if (string.IsNullOrEmpty(queryBuilder))
            {
                throw new Exception("查询条件不合法!");
            }

            if (string.IsNullOrEmpty(sort))
            {
                throw new Exception("查询临时表排序条件不合法!");
            }
            DataTable tab = this.GetDataTableByTableName(tableName);

            return tab.Select(queryBuilder, sort);
        }

        /// <summary>
        /// 移除一个临时查询表
        /// </summary>
        /// <param name="tableName">临时表名称</param>
        /// <returns></returns>
        public bool RemoveQueryTable(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("移除临时表时表名不合法");
            }
            return this.mTableSet.Remove(tableName);
        }

        /// <summary>
        /// 清空临时查询
        /// </summary>
        public void ClearQueryTable()
        {
            this.mTableSet.Clear();
        }

        # endregion

        #region 缓存更新

        /// <summary>
        /// 缓存更新数据 key 缓存表名称
        /// </summary>
        protected Dictionary<string, DataTable> mcacheForUpdate = new Dictionary<string, DataTable>(4);

        /// <summary>
        /// 判断缓存更新数据表中是否存在指定名称的表
        /// </summary>
        /// <param name="cacheName">缓存表名</param>
        /// <returns></returns>
        public bool ContainKeyUpdateCache(string cacheName)
        {
            if (string.IsNullOrEmpty(cacheName))
            {
                throw new Exception("获取更新缓存表时表名不合法");
            }
            return this.mcacheForUpdate.ContainsKey(cacheName);
        }

        /// <summary>
        /// 添加更新缓存表
        /// </summary>
        /// <param name="cacheName">缓存名称</param>
        /// <param name="cacheTable">缓存表</param>
        public void AddCacheTable(string cacheName, DataTable cacheTable)
        {
            if (this.mcacheForUpdate.ContainsKey(cacheName))
            {
                throw new Exception("添加更新缓存表的时候又重复的键:" + cacheName);
            }
            if (string.IsNullOrEmpty(cacheName))
            {
                throw new Exception("添加更新缓存表的时候表名不合法");
            }
            this.mcacheForUpdate.Add(cacheName, cacheTable);
        }

        /// <summary>
        /// 根据更新缓存表名称获取表
        /// </summary>
        /// <param name="cacheName">更新缓存表名称</param>
        /// <returns></returns>
        public DataTable GetCacheDataTable(string cacheName)
        {
            if (!this.ContainKeyUpdateCache(cacheName))
            {
                return null;
            }
            return this.mcacheForUpdate[cacheName];
        }

        /// <summary>
        /// 向缓存更新表中插入一列并返回
        /// </summary>
        /// <param name="cacheName">要插入列的缓存更新表名称</param>
        /// <returns></returns>
        public DataRow InsertNewCacheUpdateRow(string cacheName)
        {
            DataTable dt = this.GetCacheDataTable(cacheName);
            DataRow r = dt.NewRow();
            dt.Rows.Add(r);
            return r;
        }

        /// <summary>
        /// 获取更新缓存表数据修改以便以后更新
        /// </summary>
        /// <param name="cacheName">缓存表名称</param>
        /// <param name="queryBuilder">查询条件</param>
        /// <returns></returns>
        public DataRow[] GetCacheDataRowsForUpdate(string cacheName, string queryBuilder)
        {
            if (string.IsNullOrEmpty(queryBuilder))
            {
                throw new Exception("获取更新缓存表查询条件不合法!");
            }

            DataTable tab = this.GetCacheDataTable(cacheName);

            return tab.Select(queryBuilder); ;
        }

        /// <summary>
        /// 获取更新缓存表数据修改以便以后更新
        /// </summary>
        /// <param name="cacheName">缓存表名称</param>
        /// <param name="queryBuilder">查询条件</param>
        /// <param name="sort">排序条件 eg: A1 desc,A3 asc</param>
        /// <returns></returns>
        public DataRow[] GetCacheDataRowsForUpdate(string cacheName, string queryBuilder, string sort)
        {
            if (string.IsNullOrEmpty(queryBuilder))
            {
                throw new Exception("获取更新缓存表查询条件不合法!");
            }
            if (string.IsNullOrEmpty(sort))
            {
                throw new Exception("获取更新缓存表排序条件不合法!");
            }

            DataTable tab = this.GetCacheDataTable(cacheName);

            return tab.Select(queryBuilder); ;
        }

        /// <summary>
        /// 移除指定的更新缓存临时表
        /// </summary>
        /// <param name="cacheName">更新缓存临时表的名称</param>
        /// <returns></returns>
        public bool RemoveCacheUpdateTable(string cacheName)
        {
            if (string.IsNullOrEmpty(cacheName))
            {
                throw new Exception("要移除的更新缓存表名称不合法!");
            }
            return this.mcacheForUpdate.Remove(cacheName);
        }

        /// <summary>
        /// 清空更新缓存临时表
        /// </summary>
        public void ClearCacheUpdateTable()
        {
            this.mcacheForUpdate.Clear();
        }

        #endregion 缓存更新

        #region 批量更新

        /// <summary>
        /// 批量更新数据包
        /// </summary>
        protected Dictionary<string, BatchUpdateData> mUpdateBatchDatas = new Dictionary<string, BatchUpdateData>();

        /// <summary>
        /// 判断是否存在对应的批量更新项
        /// </summary>
        /// <param name="batchName"></param>
        /// <returns></returns>
        public bool ContainsKeyBatchUpdate(string batchName)
        {
            if (string.IsNullOrEmpty(batchName))
            {
                throw new Exception("判断批量更新是否存在时传入的批量更新名称不合法！");
            }
            return this.mUpdateBatchDatas.ContainsKey(batchName);
        }

        /// <summary>
        /// 添加一个批量更新内容
        /// </summary>
        /// <param name="batchName">批量更新名称</param>
        /// <param name="sql">sql语句</param>
        public void CreateOneBatchUpdate(string batchName, string sql)
        {
            if (this.ContainsKeyBatchUpdate(batchName))
            {
                throw new Exception("已经存在一个相同名称的批量更新项");
            }
            BatchUpdateData data = new BatchUpdateData(sql);
            this.mUpdateBatchDatas.Add(batchName, data);
        }

        /// <summary>
        /// 添加一个批量更新内容
        /// </summary>
        /// <param name="batchName">批量更新名称</param>
        /// <param name="sql">sql语句</param>
        /// <param name="sqlBatchMark">sql语句替换标志</param>
        public void CreateOneBatchUpdate(string batchName, string sql, string sqlBatchMark)
        {
            if (this.ContainsKeyBatchUpdate(batchName))
            {
                throw new Exception("已经存在一个相同名称的批量更新项");
            }
            BatchUpdateData data = new BatchUpdateData(sql, sqlBatchMark);
            this.mUpdateBatchDatas.Add(batchName, data);
        }

        /// <summary>
        /// 添加一个批量更新条件项
        /// </summary>
        /// <param name="batchName">批量更新名称</param>
        /// <param name="itemId">批量更新项编号</param>
        public void AddBatchUpdateItem(string batchName, decimal itemId)
        {
            if (string.IsNullOrEmpty(batchName))
            {
                throw new Exception("添加批量更新是否存在时传入的批量更新名称不合法！");
            }
            this.mUpdateBatchDatas[batchName].Add(itemId);
        }

        /// <summary>
        /// 获取一个批量更新的数据包
        /// </summary>
        /// <param name="batchName">批量更新名称</param>
        /// <returns></returns>
        public BatchUpdateData GetBatchBatchUpdateData(string batchName)
        {
            if (!this.ContainsKeyBatchUpdate(batchName))
            {
                return null;
            }

            return this.mUpdateBatchDatas[batchName];
        }

        /// <summary>
        /// 移除一个批量更新数据包
        /// </summary>
        /// <param name="batchName">批量更新名称</param>
        /// <returns></returns>
        public bool RemoveBatchUpdateData(string batchName)
        {
            if (!this.ContainsKeyBatchUpdate(batchName))
            {
                throw new Exception("没有找到" + batchName + "名称的批量更新项");
            }
            return this.mUpdateBatchDatas.Remove(batchName);
        }

        /// <summary>
        /// 清空批量更新数据包
        /// </summary>
        public void ClearBatchUpdateData()
        {
            this.mUpdateBatchDatas.Clear();
        }

        #endregion 批量更新

        #region 批量插入

        /// <summary>
        /// 批量插入数据的数据包
        /// </summary>
        protected Dictionary<string, DataTable> mInsertBatch = new Dictionary<string, DataTable>();

        /// <summary>
        /// 判断批量插入表是否已经存在
        /// </summary>
        /// <param name="insertTableName"></param>
        /// <returns></returns>
        public bool ContainsKeyBatchInsert(string insertTableName)
        {
            return this.mInsertBatch.ContainsKey(insertTableName);
        }

        /// <summary>
        /// 添加一个批量插入表格
        /// </summary>
        /// <param name="insertTableName">批量插入项目名称</param>
        /// <param name="dt">批量插入的表</param>
        public void AddBatchInsertTable(string insertTableName, DataTable dt)
        {
            this.mInsertBatch.Add(insertTableName, dt);
        }

        /// <summary>
        /// 获取制定的批量插入表格
        /// </summary>
        /// <param name="insertTableName">批量插入项目名称</param>
        /// <returns></returns>
        public DataTable GetBatchInsertTable(string insertTableName)
        {
            if (this.mInsertBatch.ContainsKey(insertTableName) == false)
            {
                return null;
            }

            return this.mInsertBatch[insertTableName];
        }

        /// <summary>
        /// 向指定的批量插入表中添加一条新行
        /// </summary>
        /// <param name="insertTableName">批量插入项目名称</param>
        /// <returns></returns>
        public DataRow InsertNewRowToBatchInsertTable(string insertTableName)
        {
            DataTable dt = this.GetBatchInsertTable(insertTableName);
            DataRow r = dt.NewRow();
            dt.Rows.Add(r);
            return r;
        }

        /// <summary>
        /// 移除制定的批量插入表格
        /// </summary>
        /// <param name="insertTableName">批量插入项目名称</param>
        /// <returns></returns>
        public bool RemoveBatchInsertRow(string insertTableName)
        {
            return this.mInsertBatch.Remove(insertTableName);
        }

        /// <summary>
        /// 清空批量插入表格
        /// </summary>
        public void ClearBatchInsertRow()
        {
            this.mInsertBatch.Clear();
        }

        #endregion 批量插入

        /// <summary>
        /// 清空所有缓存
        /// </summary>
        public void ClearAllCache()
        {
            this.ClearQueryTable();
            this.ClearCacheUpdateTable();
            this.ClearBatchUpdateData();
            this.ClearBatchInsertRow();
        }
    }

    /// <summary>
    /// 批量更新数据
    /// </summary>
    public class BatchUpdateData
    {
        /// <summary>
        /// 原始SQL
        /// </summary>
        public string OraSql = "";

        /// <summary>
        /// 默认替换文本
        /// </summary>
        public const string DefaultSqlBatchMark = "?/batch/?";

        protected string SqlBatchMark = "";

        /// <summary>
        /// 添加的条件计数
        /// </summary>
        public int Count = 0;

        /// <summary>
        /// 更新条件语句
        /// </summary>
        public StringBuilder updateBuilder = new StringBuilder();

        public BatchUpdateData(string sql)
        {
            this.OraSql = sql;
            this.SqlBatchMark = DefaultSqlBatchMark;
        }

        public BatchUpdateData(string sql, string sqlBatchMark)
        {
            this.OraSql = sql;
            this.SqlBatchMark = sqlBatchMark;
        }

        /// <summary>
        /// 添加一个十进制id数值项目
        /// </summary>
        /// <param name="id"></param>
        public void Add(decimal id)
        {
            if (this.Count > 0)
            {
                lock (this)
                {
                    this.updateBuilder.Append("," + id.ToString());
                }
            }
            else
            {
                lock (this)
                {
                    this.updateBuilder.Append(id.ToString());
                }
            }
            this.Count++;
        }

        /// <summary>
        /// 添加一个串
        /// </summary>
        /// <param name="str"></param>
        public void Add(string str)
        {
            if (string.IsNullOrWhiteSpace(str))
            {
                return;
            }
            if (this.Count > 0)
            {
                lock (this)
                {
                    this.updateBuilder.Append("," + str);
                }
            }
            else
            {
                lock (this)
                {
                    this.updateBuilder.Append(str);
                }
            }
            this.Count++;
        }

        /// <summary>
        /// 获取SQL
        /// </summary>
        public string Sql
        {
            get
            {
                if (this.Count <= 0)
                {
                    return "";
                }
                return this.OraSql.Replace(this.SqlBatchMark, this.updateBuilder.ToString());
            }
        }
    }

    /// <summary>
    /// 查询缓存数据类
    /// </summary>
    public class SqlPreparedQueryData
    {
        /// <summary>
        /// 获取或设置缓存表
        /// </summary>
        public DataTable Table { get; set; }

        /// <summary>
        /// 当前缓存包含的索引列表
        /// </summary>
        public Hashtable Indexs = new Hashtable();

        /// <summary>
        /// 根据索引名称返回对应的索引
        /// </summary>
        /// <param name="indexName"></param>
        /// <returns></returns>
        public object GetIndexByIndexName(string indexName)
        {
            if (this.Indexs.ContainsKey(indexName))
            {
                return this.Indexs[indexName];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 构造索引
        /// </summary>
        /// <param name="table"></param>
        public SqlPreparedQueryData(DataTable table)
        {
            this.Table = table;
        }

        /// <summary>
        /// 添加一个索引
        /// </summary>
        /// <param name="indexName">索引名称</param>
        /// <param name="sqlPreparedIndex">索引序列</param>
        public void AddSqlPreparedIndexSingleCol(string indexName, object sqlPreparedIndex)
        {
            this.Indexs.Add(indexName, sqlPreparedIndex);
        }

        /// <summary>
        /// 更新一个索引
        /// </summary>
        /// <param name="indexName">索引名称</param>
        /// <param name="sqlPreparedIndex">索引序列</param>
        public void UpdateSqlPreparedIndexSinlgeCol(string indexName, object sqlPreparedIndex)
        {
            this.Indexs[indexName] = sqlPreparedIndex;
        }

        /// <summary>
        /// 移除 一个索引
        /// </summary>
        /// <param name="indexName">索引名称</param>
        public void RemoveSqlPreparedIndexSingleCol(string indexName)
        {
            this.Indexs.Remove(indexName);
        }
    }
}